package report

import (
	"../common"
	"../web"
	"encoding/json"
	"strconv"
	"strings"
	"time"
)

type QueryDatesObj struct {
	common.BaseHandler
}

type QueryDatesDetailObj struct {
	common.BaseHandler
}

type QueryDatesPageObj struct {
	common.BaseHandler
}

func (self *QueryDatesObj) Prepare() {
	items := *common.FetchAllRowsPtr(
		`SELECT ITEM, ISNULL(DESCRIP, '') 
           FROM SFC_ITEMS(NOLOCK) 
          WHERE LOCKED = 0 
          ORDER BY ITEM`,
		self.Usr.Conn, new(common.ItemDesc))

	self.TmplData["items"] = items
}

func (self *QueryDatesObj) ParameterCheck() bool {
	c_begin := strings.TrimSpace(self.Ctx.Params["fromdate"])
	c_end := strings.TrimSpace(self.Ctx.Params["todate"])
	c_item := self.Ctx.Params["item"]
	c_wono := strings.TrimSpace(self.Ctx.Params["wono"])
	if c_begin == "" {
		return false
	}
	s_begin, err := time.Parse("2006-01-02 15:04:05", c_begin)
	if err != nil {
		self.TmplData["error"] = common.GetText(self.Lang, "Query Begin Date is invalid.")
		return false
	}
	if len(c_begin) != 19 {
		self.TmplData["error"] = common.GetText(self.Lang, "Query Begin Date is invalid.")
		return false
	}
	if c_end == "" {
		return false
	}
	s_end, err := time.Parse("2006-01-02 15:04:05", c_end)
	if err != nil {
		self.TmplData["error"] = common.GetText(self.Lang, "Query End Date is invalid.")
		return false
	}
	if len(c_end) != 19 {
		self.TmplData["error"] = common.GetText(self.Lang, "Query End Date is invalid.")
		return false
	}
	if c_item != "" && c_wono != "" {
		self.TmplData["error"] = common.GetText(self.Lang, "Work order / Item can only choose one.")
		return false
	}
	if c_begin == c_end {
		self.TmplData["error"] = common.GetText(self.Lang, "The selected time period is not a valid interval.")
		return false
	}
	if s_begin.After(s_end) {
		s_begin, s_end = s_end, s_begin
		c_begin, c_end = c_end, c_begin
	}
	if s_begin.Add(3600 * 24 * 180 * time.Second).Before(s_end) {
		self.TmplData["error"] = common.GetText(self.Lang, "Date interval can select up to 180 days.")
		return false
	}

	c_begin = s_begin.Format("2006-01-02 15:04:05")
	c_end = s_end.Format("2006-01-02 15:04:05")

	self.Sqlparas = append(self.Sqlparas, c_begin)
	self.Sqlparas = append(self.Sqlparas, c_end)
	self.Sqlparas = append(self.Sqlparas, c_wono)
	self.Sqlparas = append(self.Sqlparas, c_item)
	self.TmplData["from_datetime"] = c_begin
	self.TmplData["to_datetime"] = c_end
	self.TmplData["item"] = c_item
	if c_wono == "" {
		c_wono = "All"
	}
	self.TmplData["wono"] = c_wono

	return true
}

func (self *QueryDatesObj) SqlQueryExecute() {
	c_begin := self.Sqlparas[0].(string)
	c_end := self.Sqlparas[1].(string)
	c_wono := self.Sqlparas[2].(string)
	c_item := self.Sqlparas[3].(string)
	var sRtnData []interface{}
	if c_item == "" {
		if c_wono == "" {
			sRtnData = *common.FetchAllRowsPtr(
				`SELECT X.ITEM,Y.DESCRIP,X.CURR_STATION,X.RESULT,X.CNT FROM (
						SELECT C.ITEM, A.CURR_STATION, A.RESULT, COUNT(A.RESULT) AS CNT 
   						  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
  						 WHERE A.MODIFYDATE BETWEEN ? AND ? 
    					   AND A.SSN_NO=B.SSN_NO 
    					   AND B.WONO=C.WONO 
  						 GROUP BY C.ITEM, A.CURR_STATION, A.RESULT) X
				   JOIN SFC_ITEMS Y(NOLOCK)
					 ON X.ITEM=Y.ITEM`,
				self.Usr.Conn, new(common.DateQueryPyCount), c_begin, c_end)
		} else {
			sRtnData = *common.FetchAllRowsPtr(
				`SELECT X.ITEM,Y.DESCRIP,X.CURR_STATION,X.RESULT,X.CNT FROM (
						SELECT C.ITEM, A.CURR_STATION, A.RESULT, COUNT(A.RESULT) AS CNT 
   						  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
  						 WHERE A.MODIFYDATE BETWEEN ? AND ? 
    					   AND A.SSN_NO=B.SSN_NO 
    					   AND B.WONO=? 
    					   AND B.WONO=C.WONO 
  						 GROUP BY C.ITEM, A.CURR_STATION, A.RESULT) X
				   JOIN SFC_ITEMS Y(NOLOCK)
					 ON X.ITEM=Y.ITEM`,
				self.Usr.Conn, new(common.DateQueryPyCount), c_begin, c_end, c_wono)
		}
	} else {
		sRtnData = *common.FetchAllRowsPtr(
			`SELECT D.ITEM, D.DESCRIP, A.CURR_STATION, A.RESULT, COUNT(A.RESULT) AS CNT 
               FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
              WHERE A.MODIFYDATE BETWEEN ? AND ? 
                AND A.SSN_NO=B.SSN_NO 
                AND B.WONO=C.WONO 
                AND C.ITEM=? 
                AND C.ITEM=D.ITEM 
              GROUP BY D.ITEM, D.DESCRIP, A.CURR_STATION, A.RESULT`,
			self.Usr.Conn, new(common.DateQueryPyCount), c_begin, c_end, c_item)
	}

	var dataAll []*common.RprByDate
	dictAll := make(map[string]*common.RprByDate)
	for _, val := range sRtnData {
		q_item := strings.TrimSpace(val.(common.DateQueryPyCount).Item)
		q_desc := strings.TrimSpace(val.(common.DateQueryPyCount).Desc)
		q_stat := strings.TrimSpace(val.(common.DateQueryPyCount).Curr)
		q_rslt := strings.TrimSpace(val.(common.DateQueryPyCount).Result)
		q_cunt := val.(common.DateQueryPyCount).Count
		v, ok := dictAll[q_item]
		if !ok {
			v = new(common.RprByDate)
			v.Item = strings.TrimSpace(q_item)
			v.Desc = strings.TrimSpace(q_desc)
			v.Leng = 2
			v.Data = map[interface{}][]interface{}{
				q_stat: []interface{}{0, 0, "0.0", 0, 0, "0.0"},
			}
			dictAll[q_item] = v
		}

		if _, ok := v.Data[q_stat]; !ok {
			v.Leng++
			v.Data[q_stat] = []interface{}{0, 0, "0.0", 0, 0, "0.0"}
		}
		ls_sta := v.Data[q_stat]
		if q_rslt == "PASS" {
			ls_sta[0] = ls_sta[0].(int) + q_cunt
			v.SubPyPass = v.SubPyPass + q_cunt
		}
		if q_rslt == "FAIL" {
			ls_sta[1] = ls_sta[1].(int) + q_cunt
			v.SubPyFail = v.SubPyFail + q_cunt
		}
	}

	if c_item == "" {
		if c_wono == "" {
			sRtnData = *common.FetchAllRowsPtr(
				`WITH 
                 CT1 AS 
                 (
                 	SELECT C.ITEM, A.CURR_STATION, A.RESULT, A.SSN_NO 
                 	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK) 
                     WHERE A.MODIFYDATE BETWEEN ? AND ? 
                       AND A.SSN_NO=B.SSN_NO 
                       AND B.WONO=C.WONO                          
                 ), 
                 CT2 AS 
                 (
                    SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1
                     WHERE CT1.RESULT IN ('FAIL', 'NDF') 
                 ), 
                 CT3 AS 
                 (
                    SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1 
                     WHERE CT1.RESULT='PASS' AND CT1.SSN_NO NOT IN(SELECT CT2.SSN_NO FROM CT2 WHERE CT1.CURR_STATION=CT2.CURR_STATION AND CT1.SSN_NO=CT2.SSN_NO)
                 ) 
                 SELECT CT2.ITEM, CT2.CURR_STATION, CT2.RESULT, COUNT(CT2.SSN_NO) AS CNT FROM CT2 
                  GROUP BY CT2.ITEM, CT2.CURR_STATION, CT2.RESULT
                  UNION 
                 SELECT CT3.ITEM, CT3.CURR_STATION, CT3.RESULT, COUNT(CT3.SSN_NO) AS CNT FROM CT3
                  GROUP BY CT3.ITEM, CT3.CURR_STATION, CT3.RESULT`,
				self.Usr.Conn, new(common.DateQueryFpyCount), c_begin, c_end)
		} else {
			sRtnData = *common.FetchAllRowsPtr(
				`WITH 
                 CT1 AS 
                 (
                    SELECT C.ITEM, A.CURR_STATION, A.RESULT, A.SSN_NO 
                      FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK) 
                     WHERE A.MODIFYDATE BETWEEN ? AND ? 
                       AND A.SSN_NO=B.SSN_NO 
                       AND B.WONO=? 
                       AND B.WONO=C.WONO                          
                 ), 
                 CT2 AS 
                 (
                    SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1
                     WHERE CT1.RESULT IN ('FAIL', 'NDF') 
                 ), 
                 CT3 AS 
                 (
                    SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1 
                     WHERE CT1.RESULT='PASS' AND CT1.SSN_NO NOT IN(SELECT CT2.SSN_NO FROM CT2 WHERE CT1.CURR_STATION=CT2.CURR_STATION AND CT1.SSN_NO=CT2.SSN_NO)
                 ) 
                 SELECT CT2.ITEM, CT2.CURR_STATION, CT2.RESULT, COUNT(CT2.SSN_NO) AS CNT FROM CT2 
                  GROUP BY CT2.ITEM, CT2.CURR_STATION, CT2.RESULT
                  UNION 
                 SELECT CT3.ITEM, CT3.CURR_STATION, CT3.RESULT, COUNT(CT3.SSN_NO) AS CNT FROM CT3
                  GROUP BY CT3.ITEM, CT3.CURR_STATION, CT3.RESULT`,
				self.Usr.Conn, new(common.DateQueryFpyCount), c_begin, c_end, c_wono)
		}
	} else {
		sRtnData = *common.FetchAllRowsPtr(
			`WITH 
             CT1 AS 
             ( 
                SELECT C.ITEM, A.CURR_STATION, A.RESULT, A.SSN_NO 
                  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK) 
                 WHERE A.MODIFYDATE BETWEEN ? AND ? 
                   AND A.SSN_NO=B.SSN_NO 
                   AND B.WONO=C.WONO 
                   AND C.ITEM=?                      
             ), 
             CT2 AS 
             ( 
                SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1 
                 WHERE CT1.RESULT IN ('FAIL', 'NDF') 
             ), 
             CT3 AS 
             ( 
                SELECT CT1.ITEM, CT1.CURR_STATION, CT1.RESULT, CT1.SSN_NO FROM CT1 
                 WHERE CT1.RESULT='PASS' AND CT1.SSN_NO NOT IN(SELECT CT2.SSN_NO FROM CT2 WHERE CT1.CURR_STATION=CT2.CURR_STATION AND CT1.SSN_NO=CT2.SSN_NO)
             ) 
             SELECT CT2.ITEM, CT2.CURR_STATION, CT2.RESULT, COUNT(CT2.SSN_NO) AS CNT FROM CT2 
              GROUP BY CT2.ITEM, CT2.CURR_STATION, CT2.RESULT
              UNION 
             SELECT CT3.ITEM, CT3.CURR_STATION, CT3.RESULT, COUNT(CT3.SSN_NO) AS CNT FROM CT3
              GROUP BY CT3.ITEM, CT3.CURR_STATION, CT3.RESULT`,
			self.Usr.Conn, new(common.DateQueryFpyCount), c_begin, c_end, c_item)
	}

	for _, val := range sRtnData {
		q_item := strings.TrimSpace(val.(common.DateQueryFpyCount).Item)
		q_stat := strings.TrimSpace(val.(common.DateQueryFpyCount).Curr)
		q_rslt := strings.TrimSpace(val.(common.DateQueryFpyCount).Result)
		q_cunt := val.(common.DateQueryFpyCount).Count
		v, ok := dictAll[q_item]
		if !ok {
			continue
		}
		c_stat := v.Data
		if _, ok := c_stat[q_stat]; !ok {
			continue
		}
		curr_sta := c_stat[q_stat]
		if q_rslt == "PASS" {
			curr_sta[3] = curr_sta[3].(int) + q_cunt
			v.SubFpyPass = v.SubFpyPass + q_cunt
			continue
		}
		if q_rslt == "FAIL" {
			curr_sta[4] = curr_sta[4].(int) + q_cunt
			v.SubFpyFail = v.SubFpyFail + q_cunt
			continue
		}
	}

	for _, v := range dictAll {
		dataAll = append(dataAll, v)
	}

	alltotal := new(common.RprByDate)
	totalfpyv := 1.0
	for _, c_item := range dataAll {
		alltotal.SubPyPass = alltotal.SubPyPass + c_item.SubPyPass
		alltotal.SubPyFail = alltotal.SubPyFail + c_item.SubPyFail
		alltotal.SubFpyPass = alltotal.SubFpyPass + c_item.SubFpyPass
		alltotal.SubFpyFail = alltotal.SubFpyFail + c_item.SubFpyFail
		c_item.Leng++
		subpyv := 100.0                                 // py default
		subfpyv := 1.0                                  // fpy default
		i_ptotal := c_item.SubPyPass + c_item.SubPyFail // py total
		if c_item.SubPyPass == 0 {
			subpyv = 0.0
		} else if i_ptotal > 0 {
			subpyv = float64(c_item.SubPyPass) * 1.0 / float64(i_ptotal) * 100.0
		}
		if subpyv > 0.0 && subpyv < 100.0 {
			c_item.SubPy = strconv.FormatFloat(subpyv, 'f', 2, 64)
		} else {
			c_item.SubPy = strconv.FormatFloat(subpyv, 'f', 1, 64)
		}

		c_stat := c_item.Data
		for _, val := range c_stat {
			pyv := 100.0            // py default
			fpyv := 100.0           // fpy default
			i_pp := val[0].(int)    // py PASS
			i_pf := val[1].(int)    // py FAIL
			i_fp := val[3].(int)    // fpy PASS
			i_ff := val[4].(int)    // fpy FAIL
			i_ptotal := i_pp + i_pf // py total
			i_ftotal := i_fp + i_ff // fpy total
			i_ppass := i_pp         // py PASS
			i_fpass := i_fp         // fpy PASS
			if i_ppass == 0 {
				pyv = 0.0
			} else if i_ptotal > 0 {
				pyv = float64(i_ppass) * 1.0 / float64(i_ptotal) * 100.0
			}
			if pyv > 0.0 && pyv < 100.0 {
				val[2] = strconv.FormatFloat(pyv, 'f', 2, 64)
			} else {
				val[2] = strconv.FormatFloat(pyv, 'f', 1, 64)
			}
			if i_fpass == 0 {
				fpyv = 0.0
			} else if i_ftotal > 0 {
				fpyv = float64(i_fpass) * 1.0 / float64(i_ftotal) * 100.0
			}
			if fpyv > 0.0 && fpyv < 100.0 {
				val[5] = strconv.FormatFloat(fpyv, 'f', 2, 64)
				subfpyv = subfpyv * fpyv / 100.0
			} else {
				val[5] = strconv.FormatFloat(fpyv, 'f', 1, 64)
			}
		}

		if subfpyv > 0.0 && subfpyv < 1.0 {
			c_item.SubFpy = strconv.FormatFloat(subfpyv*100.0, 'f', 2, 64)
			totalfpyv = totalfpyv * subfpyv
		} else {
			c_item.SubFpy = strconv.FormatFloat(subfpyv*100.0, 'f', 1, 64)
		}
	}

	alltotal.Item = ""
	alltotal.Desc = ""
	alltotal.Leng = 1

	pyv := 100.0
	i_ptotal := alltotal.SubPyPass + alltotal.SubPyFail
	if alltotal.SubPyPass == 0 {
		pyv = 0.0
	} else if i_ptotal > 0 {
		pyv = float64(alltotal.SubPyPass) * 1.0 / float64(i_ptotal) * 100.0
	}
	if pyv > 0.0 && pyv < 100.0 {
		alltotal.SubPy = strconv.FormatFloat(pyv, 'f', 2, 64)
	} else {
		alltotal.SubPy = strconv.FormatFloat(pyv, 'f', 1, 64)
	}
	if totalfpyv > 0.0 && totalfpyv < 1.0 {
		alltotal.SubFpy = strconv.FormatFloat(totalfpyv*100.0, 'f', 2, 64)
	} else {
		alltotal.SubFpy = strconv.FormatFloat(totalfpyv*100.0, 'f', 1, 64)
	}

	if c_wono == "" {
		c_wono = "All"
	}
	if c_item == "" {
		c_item = "All"
	}

	self.TmplData["dict_rst"] = dataAll
	self.TmplData["alltotal"] = alltotal
}

func QueryByDateHandler(ctx *web.Context) {
	self := new(QueryDatesObj)
	self.Ctx = ctx
	self.Html = "querybydate.html"
	self.Rightlist = []string{"querybydate"}
	self.DoQuery(self)
}

func (self *QueryDatesDetailObj) ParameterCheck() bool {
	s_nm := self.Ctx.Params["nm"] // query result data number
	s_fd := self.Ctx.Params["fd"] // begin date
	s_td := self.Ctx.Params["td"] // end date
	s_im := self.Ctx.Params["im"] // item
	s_wo := self.Ctx.Params["wo"] // work order
	s_st := self.Ctx.Params["st"] // station
	s_tp := self.Ctx.Params["tp"] // PY/FPY
	s_ts := self.Ctx.Params["ts"] // PASS/FAIL/NDF
	if s_nm == "" || s_fd == "" || s_td == "" || s_im == "" || s_wo == "" ||
		s_st == "" || s_tp == "" || s_ts == "" {
		return false
	}

	rec_curr, _ := strconv.Atoi(s_nm)
	total_id := rec_curr / 10
	if rec_curr%10 > 0 {
		total_id++
	}

	self.Sqlparas = append(self.Sqlparas, s_fd)
	self.Sqlparas = append(self.Sqlparas, s_td)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_wo)
	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_tp)
	self.Sqlparas = append(self.Sqlparas, s_ts)

	self.TmplData["curr_page"] = "1"
	self.TmplData["total_page"] = total_id
	self.TmplData["fromdate"] = s_fd
	self.TmplData["todate"] = s_td
	self.TmplData["item"] = s_im
	self.TmplData["wono"] = s_wo
	self.TmplData["station"] = s_st
	self.TmplData["qtyp"] = s_tp
	self.TmplData["status"] = s_ts

	return true
}

func (self *QueryDatesDetailObj) SqlQueryExecute() {
	ssn_main := *common.FetchAllRowsPtr(
		`sp_QueryByDateDetails ?, ?, ?, ?, ?, ?, ?`,
		self.Usr.Conn, new(common.QueryDetail), self.Sqlparas...)

	self.TmplData["ssn_main"] = ssn_main
}

func DateByDetailHandler(ctx *web.Context) {
	self := new(QueryDatesDetailObj)
	self.Ctx = ctx
	self.Html = "querybydatedetail.html"
	self.Rightlist = []string{"querybydate"}
	self.DoQueryUrl(self)
}

func (self *QueryDatesPageObj) ParameterCheck() bool {
	pageid := self.Ctx.Params["pageid"] // current page
	s_fd := self.Ctx.Params["fd"]       // begin date
	s_td := self.Ctx.Params["td"]       // end date
	s_im := self.Ctx.Params["im"]       // item
	s_wo := self.Ctx.Params["wo"]       // work order
	s_st := self.Ctx.Params["st"]       // station
	s_tp := self.Ctx.Params["tp"]       // PY/FPY
	s_ts := self.Ctx.Params["ts"]       // PASS/FAIL/NDF

	s_id, err := strconv.Atoi(pageid)
	if err != nil {
		return false
	}
	if s_fd == "" {
		return false
	}
	if s_td == "" {
		return false
	}
	if s_im == "" {
		return false
	}
	if s_wo == "" {
		return false
	}
	if s_st == "" {
		return false
	}
	if s_tp == "" {
		return false
	}
	if s_ts == "" {
		return false
	}

	idx := 10 * (s_id - 1)

	self.Sqlparas = append(self.Sqlparas, s_fd)
	self.Sqlparas = append(self.Sqlparas, s_td)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_wo)
	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_tp)
	self.Sqlparas = append(self.Sqlparas, s_ts)
	self.Sqlparas = append(self.Sqlparas, idx)

	return true
}

func (self *QueryDatesPageObj) SqlQueryExecute() {
	ssn_main := *common.FetchAllRowsPtr(
		`sp_QueryByDatePerPageDetails ?, ?, ?, ?, ?, ?, ?, ?`,
		self.Usr.Conn, new(common.QueryDetail), self.Sqlparas...)

	dict_js, _ := json.Marshal(map[string]interface{}{"details": ssn_main, "lang": self.Lang})
	self.Ctx.ResponseWriter.Write(dict_js)
}

func SplitDateQueryHandler(ctx *web.Context) {
	self := new(QueryDatesPageObj)
	self.Ctx = ctx
	self.DoJsonQuery(self)
}
